package superhero.data;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

/**
 *
 * @author computer
 */
public class SuperheroDBQuery
{
    // Database URL Constant

    public final String DB_URL =
            "jdbc:derby:SuperheroDB";
    private Connection conn;      // Database connection
    private String[][] tableData; // Table data
    private String[] colNames;    // Column names
    private String[] records;
    private String factionId;
    private int numRows;

    public SuperheroDBQuery()
    {
        getDatabaseConnection();
    }

    /**
     * Constructor
     */
    public SuperheroDBQuery(String query)
    {
        // Get a connection to the database.
        getDatabaseConnection();

        try
        {
            // Create a Statement object for the query.
            Statement stmt =
                    conn.createStatement(
                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);

            // Execute the query.
            ResultSet resultSet =
                    stmt.executeQuery(query);

            // Get the number of rows.
            resultSet.last();                 // Move to last row
            numRows = resultSet.getRow(); // Get row number
            resultSet.first();                // Move to first row
            records = new String[numRows];

            // Get a metadata object for the result set.
            ResultSetMetaData meta = resultSet.getMetaData();

            // Create an array of Strings for the column names.
            colNames = new String[meta.getColumnCount()];

            // Store the column names in the colNames array.
            for (int i = 0;
                    i < meta.getColumnCount();
                    i++)
            {
                // Get a column name.
                colNames[i] = meta.getColumnLabel(i + 1);
            }

            // Create a 2D String array for the table data.
            tableData =
                    new String[numRows][meta.getColumnCount()];


            // Store the columns in the tableData array.
            for (int row = 0;
                    row < numRows;
                    row++)
            {
                for (int col = 0;
                        col < meta.getColumnCount();
                        col++)
                {
                    tableData[row][col] = resultSet.getString(col + 1);
                }
                records[row] = resultSet.getString(1).trim();
                // Go to the next row in the ResultSet.
                resultSet.next();
            }



            // Close the statement and connection objects.
            stmt.close();
            conn.close();
        }
        catch (SQLException ex)
        {
            ex.printStackTrace();
        }
    }

    public void closeConnection()
    {
        try
        {
            conn.close();
        }
        catch (SQLException ex)
        {
            ex.printStackTrace();
            System.err.println("Error while clising DB.");
            System.exit(0);
        }
    }

    public boolean addSuperHero(String factionName,
            String name,
            String realName,
            String powers)
    {
        addFaction(factionName);
        boolean result = true;        

        try
        {
            Statement state =
                    conn.createStatement(
                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);

            String insert;
            insert = "INSERT INTO Characters VALUES"
                    + "('" + factionId + "', '" + name + "', '"
                    + realName + "', '" + powers + "')";

            state.executeUpdate(insert);
            state.close();
        }
        catch (Exception ex)
        {
            System.out.println("ERROR: " + ex.getMessage());
            result = false;
        }
        return result;
    }

    public String[] getFactionNames()
    {
        String[] results = null;
        try
        {
            Statement stmt =
                    conn.createStatement(
                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);

            String query = "SELECT * FROM Factions";

            // Execute the query.
            ResultSet resultSet =
                    stmt.executeQuery(query);

            // Get the number of rows.
            resultSet.last();                 // Move to last row
            numRows = resultSet.getRow(); // Get row number
            resultSet.first();                // Move to first row          

            // Get a metadata object for the result set.
            ResultSetMetaData meta = resultSet.getMetaData();

            // Create an array of Strings for the column names.
            colNames = new String[meta.getColumnCount()];

            // Store the column names in the colNames array.
            for (int i = 0;
                    i < meta.getColumnCount();
                    i++)
            {
                // Get a column name.
                colNames[i] = meta.getColumnLabel(i + 1);
            }

            // Create a 2D String array for the table data.
            tableData =
                    new String[numRows][meta.getColumnCount()];


            // Store the columns in the tableData array.
            for (int row = 0;
                    row < numRows;
                    row++)
            {
                for (int col = 0;
                        col < meta.getColumnCount();
                        col++)
                {
                    tableData[row][col] = resultSet.getString(col + 1);
                }

                // Go to the next row in the ResultSet.
                resultSet.next();
            }
        }
        catch (SQLException ex)
        {
            //put somethign usefull here...
        }
        return results;
    }

    public boolean addFaction(String faction)
    {
        getFactionNames();
        String insert = null;        
        int id;

        try
        {
            Statement stat =
                    conn.createStatement(
                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);

            for(int i = 0; i < tableData.length; i++)
            {
                if (faction.equalsIgnoreCase(tableData[i][1].toString().trim()))
                {
//                    insert = "INSERT INTO Factions VALUES "
//                            + "('" + tableData[i][0].toString().trim() + "', '"
//                            + faction + "')";

                    factionId = tableData[i][0].toString().trim();
                    insert = null;
                    break;
                }
                else
                {
                    id = Integer.parseInt(tableData[i][0].toString().trim()) + 1;
                    factionId = id + "";
                    insert = "INSERT INTO Factions VALUES"
                            + "('" + factionId + "', '" + faction + "')";                    
                }         
              
            }


            //System.err.println(insert);
            if(!(insert == null))
            {
                stat.executeUpdate(insert);
            }
            
        }
        catch (SQLException ex)
        {
            System.err.println("ERROR: " + ex.getMessage());
        }

        return false;
    }
    
    public boolean updateRecord(String name, String realName, String powers)
    {
        try
        {
            Statement sta =
                    conn.createStatement(
                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);

            String editInfo = "UPDATE Characters "
                                    + "SET CharacterName = '" + name + "', "
                                    + "RealName = '" + realName + "', "
                                    + "Powers = '" + powers + "' "
                                    + "WHERE CharacterName = '" + name + "'";

            sta.executeUpdate(editInfo);
            sta.close();
        }
        catch (Exception ex)
        {
            System.out.println("ERROR: " + ex.getMessage());
            return false;
        }
        return true;
    }

    /**
     * The getDatabaseConnection method loads the JDBC and gets a connection to
     * the database.
     */
    private void getDatabaseConnection()
    {
        try
        {
            // Create a connection to the database.
            conn = DriverManager.getConnection(DB_URL);
        }
        catch (Exception ex)
        {
            ex.printStackTrace();
            System.exit(0);
        }
    }

    /**
     * The getColumnNames method returns the column names.
     */
    public String[] getColumnNames()
    {
        return colNames;
    }

    /**
     * The getTableData method returns the table data.
     */
    public String[][] getTableData()
    {
        return tableData;
    }

    /**
     * @return the records
     */
    public String[] getRecords()
    {
        return records;
    }
}
